﻿
Option Strict On

Imports KUeLib.Attachments
Imports DbDataLib
Imports System.Text
Imports System.Data
Imports System.Data.Common

Namespace Services

    Public Class FileAttachmentDataService
        Inherits ServiceDefinitions.FileAttachmentDataService

        Public Sub New(ByVal dataService As DbDataService)
            MyBase.New(dataService)
        End Sub

        Protected Function GetLastInsertId() As Integer
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New Text.StringBuilder

            sb.AppendLine("SELECT last_insert_id()")

            cmd.CommandText = sb.ToString()

            Return CInt(_dataService.ExecuteScalar(cmd))
        End Function

        Public Overrides Function GetAttachmentForProcess(ByVal procId As Integer) As List(Of FileAttachment)
            Dim sb As New StringBuilder
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim dt As DataTable
            Dim lofa As New List(Of FileAttachment)

            sb.AppendLine("SELECT * ")
            sb.AppendLine("FROM file_attachments ")
            sb.AppendLine("WHERE fa_process = @p_process ")
            sb.AppendLine("ORDER BY fa_datum ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_process", DbType.Int32, procId))

            dt = _dataService.GetDataTable(cmd)

            For i As Integer = 0 To dt.Rows.Count - 1 Step 1
                With dt.Rows(i)
                    Dim fa As New FileAttachment
                    fa.Id = CInt(!fa_id)
                    fa.ProcessId = CInt(!fa_process)
                    fa.Bezeichnung = CStr(Utils.DbNull2Nothing(!fa_bezeichnung))
                    fa.Filename = CStr(Utils.DbNull2Nothing(!fa_filename))
                    fa.Datum = CDate(Utils.DbNull2Nothing(!fa_datum))
                    lofa.Add(fa)
                End With
            Next

            Return lofa

        End Function

        Public Overrides Sub FillBinary(ByRef fileAttachment As FileAttachment)
            Dim sb As New StringBuilder
            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()

            sb.AppendLine("SELECT fa_binary FROM file_attachments WHERE fa_id = @p_id ")
            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Binary, fileAttachment.Id))

            fileAttachment.File = CType(_dataService.ExecuteScalar(cmd), Byte())
        End Sub

        Public Overloads Overrides Sub SaveBinary(ByVal fileAttachment As FileAttachment, ByVal binary As Byte())

            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New StringBuilder

            sb.AppendLine("UPDATE file_attachments SET fa_binary = @p_file ")
            sb.AppendLine("WHERE fa_id = @p_id ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_file", DbType.Binary, binary))
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, fileAttachment.Id))

            _dataService.ExecuteNonQuery(cmd)

        End Sub

        Public Overloads Overrides Sub SaveBinary(ByVal fileAttachment As FileAttachment)

            If fileAttachment.File Is Nothing Then _
                Throw New NullReferenceException("Die Property File ist leer.")

            SaveBinary(fileAttachment, fileAttachment.File)

        End Sub

        Public Overrides Function InsertNewFileAttachment(ByVal fileAttachment As FileAttachment) As FileAttachment

            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New StringBuilder

            sb.AppendLine("INSERT INTO file_attachments(fa_process, fa_bezeichnung, fa_filename, fa_datum) ")
            sb.AppendLine("VALUES(@p_process, @p_bezeichnung, @p_filename, now())")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_process", DbType.Int32, fileAttachment.ProcessId))
            cmd.Parameters.Add(_dataService.CreateParameter("p_bezeichnung", DbType.String, fileAttachment.Bezeichnung))
            cmd.Parameters.Add(_dataService.CreateParameter("p_filename", DbType.String, fileAttachment.Filename))

            _dataService.ExecuteNonQuery(cmd)

            fileAttachment.Id = GetLastInsertId()

            If fileAttachment.File IsNot Nothing Then _
                SaveBinary(fileAttachment)

            Return fileAttachment

        End Function

        Public Overrides Function DeleteProcess(ByVal processId As Integer) As Integer

            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New StringBuilder

            sb.AppendLine("DELETE FROM file_attachments WHERE fa_process = @p_process ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_process", DbType.Int32, processId))

            Return _dataService.ExecuteNonQuery(cmd)

        End Function

        Public Overrides Sub Delete(ByVal fileAttachment As FileAttachment)
            Delete(fileAttachment.Id)
        End Sub

        Public Overrides Sub Delete(ByVal fileAttachmentId As Integer)

            Dim cmd As DbCommand = _dataService.Factory.CreateCommand()
            Dim sb As New StringBuilder

            sb.AppendLine("DELETE FROM file_attachments WHERE fa_id = @p_id ")

            cmd.CommandText = sb.ToString()
            cmd.Parameters.Add(_dataService.CreateParameter("p_id", DbType.Int32, fileAttachmentId))

            _dataService.ExecuteNonQuery(cmd)
        End Sub

    End Class

End Namespace
